Create Calculated Columns and Measures 9
Create Measures and Calculated Columns
ยท MEASURE AND COLUMN CALCULATION
DAX Calculation for measure
2.
TotalTickets = DISTINCTCOUNT(Fact_Zendesk [TicketID])
3.
Resolved Tickets = CALCULATE([TotalTickets],
Dim_Status[StatusKey]=1)+CALCULATE([TotalTickets],Dim_Status[StatusKey]=2)
4.
OpenTickets =
CALCULATE([TotalTickets],Dim_Status[StatusKey]=3)+CALCULATE([TotalTickets],Dim_Status[StatusKey]=4)+CALCULATE([TotalTickets],Dim_Status[StatusKey]=6)
5.
MTD 1stResTime = CALCULATE([Avg_Res_Time],DATESMTD('Date'[Date]))
6.
LM_Tickets =
TOTALMTD([TotalticketsNoDeleted],DATEADD('Date'[Date],-1,MONTH))
7.
LM_RollingResolved = CALCULATE([Resolved Tickets],DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-30,DAY))
8.
LM_Resolved = TOTALMTD([Resolved
Tickets],DATEADD('Date'[Date],-1,MONTH))
9.
FirstContactRate = AVERAGE(Fact_Zendesk[FirstContactResolutionRate])
10.
Deleted Tickets = CALCULATE([TotalTickets],Dim_Status[StatusKey]=5)
11.
CurrnetMonth_Tickets = TOTALMTD([TotalticketsNoDeleted],'Date'[Date])
12.
CurrnetMonth_Resolved = TOTALMTD([Resolved Tickets],'Date'[Date])
13.
CurMonth1stResTime = CALCULATE([Avg_Res_Time],DATESMTD('Date'[Date]))
14.
AvgTime to close = AVERAGE(Fact_Zendesk[TimeToCloseDays])
15.
AvgClosingDays = AVERAGE(Fact_Zendesk[TimeToCloseDays])
16.
.YTD AvgReplayTime = TOTALYTD([Avg_Res_Time],'Date'[Date])
17.
.SamePeriodAVGReplaytime = CALCULATE([Avg_Res_Time],SAMEPERIODLASTYEAR('Date'[Date]))
18.
.PrevM_TimetocloseDays = CALCULATE([AvgClosingDays],DATEADD('Date'[Date],-1,MONTH))
19.
.MTD_TimetocloseDays = CALCULATE([AvgTime to close],DATESMTD('Date'[Date]))
20.
.MTD_FirstContactRate = CALCULATE([FirstContactRate],DATESMTD('Date'[Date]))
21. .CurM_resolved% = [CurrnetMonth_Resolved]/[CurrnetMonth_Tickets]
- DAX for calculated
column
1.
DayofWeek = FORMAT(Dim_Ticket[Created],"dddd")
2.
FirstRes = DATEDIFF(Dim_Ticket[Created],Dim_Ticket[RspDate],HOUR)
3.
Month_shortCC = LEFT(Dim_Ticket[Month],3)
4.
CreatedHours = HOUR(Dim_Ticket[Created])
5.
CloseTime = DATEDIFF(Dim_Ticket[Created],Dim_Ticket[Updated],HOUR)
Create Measures and Calculated Columns
ยท MEASURE AND COLUMN CALCULATION
DAX Calculation for measure
2.
TotalTickets = DISTINCTCOUNT(Fact_Zendesk [TicketID])
3.
Resolved Tickets = CALCULATE([TotalTickets],
Dim_Status[StatusKey]=1)+CALCULATE([TotalTickets],Dim_Status[StatusKey]=2)
4.
OpenTickets =
CALCULATE([TotalTickets],Dim_Status[StatusKey]=3)+CALCULATE([TotalTickets],Dim_Status[StatusKey]=4)+CALCULATE([TotalTickets],Dim_Status[StatusKey]=6)
5.
MTD 1stResTime = CALCULATE([Avg_Res_Time],DATESMTD('Date'[Date]))
6.
LM_Tickets =
TOTALMTD([TotalticketsNoDeleted],DATEADD('Date'[Date],-1,MONTH))
7.
LM_RollingResolved = CALCULATE([Resolved Tickets],DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-30,DAY))
8.
LM_Resolved = TOTALMTD([Resolved
Tickets],DATEADD('Date'[Date],-1,MONTH))
9.
FirstContactRate = AVERAGE(Fact_Zendesk[FirstContactResolutionRate])
10.
Deleted Tickets = CALCULATE([TotalTickets],Dim_Status[StatusKey]=5)
11.
CurrnetMonth_Tickets = TOTALMTD([TotalticketsNoDeleted],'Date'[Date])
12.
CurrnetMonth_Resolved = TOTALMTD([Resolved Tickets],'Date'[Date])
13.
CurMonth1stResTime = CALCULATE([Avg_Res_Time],DATESMTD('Date'[Date]))
14.
AvgTime to close = AVERAGE(Fact_Zendesk[TimeToCloseDays])
15.
AvgClosingDays = AVERAGE(Fact_Zendesk[TimeToCloseDays])
16.
.YTD AvgReplayTime = TOTALYTD([Avg_Res_Time],'Date'[Date])
17.
.SamePeriodAVGReplaytime = CALCULATE([Avg_Res_Time],SAMEPERIODLASTYEAR('Date'[Date]))
18.
.PrevM_TimetocloseDays = CALCULATE([AvgClosingDays],DATEADD('Date'[Date],-1,MONTH))
19.
.MTD_TimetocloseDays = CALCULATE([AvgTime to close],DATESMTD('Date'[Date]))
20.
.MTD_FirstContactRate = CALCULATE([FirstContactRate],DATESMTD('Date'[Date]))
21. .CurM_resolved% = [CurrnetMonth_Resolved]/[CurrnetMonth_Tickets]
- DAX for calculated
column
1.
DayofWeek = FORMAT(Dim_Ticket[Created],"dddd")
2.
FirstRes = DATEDIFF(Dim_Ticket[Created],Dim_Ticket[RspDate],HOUR)
3.
Month_shortCC = LEFT(Dim_Ticket[Month],3)
4.
CreatedHours = HOUR(Dim_Ticket[Created])
5.
CloseTime = DATEDIFF(Dim_Ticket[Created],Dim_Ticket[Updated],HOUR)